package cn.javaex.hgo.action.system.service.hgo_demo;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.crypto.hash.Md5Hash;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import cn.javaex.hgo.action.system.dao.hgo_user_info.IHgoUserInfoDAO;
import cn.javaex.hgo.action.system.view.HgoPoiDemo;
import cn.javaex.hgo.action.system.view.HgoUserInfo;
import cn.javaex.hgo.util.HgoExcelTemplateEntity;
import cn.javaex.hgo.util.HgoPoiUtils;

@Service("HgoPoiDemoService")
public class HgoPoiDemoService {
	
	@Autowired
	private IHgoUserInfoDAO iHgoUserInfoDAO;
	
	/**
	 * 导出Excel
	 * @throws Exception
	 */
	public void exportExcelXlsx() throws Exception {
		XSSFWorkbook xwb = new XSSFWorkbook();
		
		// 头部
		String[] headNameArr = {"账号", "真实姓名", "用户类型", "用户邮箱", "用户手机号"};
		
		// webbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = xwb.createSheet("数据");
		// 表头
		XSSFRow row = sheet.createRow(0);
		for (int i=0; i<headNameArr.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellValue(headNameArr[i]);
		}
		
		// 数据
		List<HgoUserInfo> list = iHgoUserInfoDAO.list(null);
		if (list!=null && list.isEmpty()==false) {
			int rowIndex = 1;
			int len = list.size();
			for (int i=0; i<len; i++) {
				row = sheet.createRow(rowIndex);
				
				HgoUserInfo entity = list.get(i);
				
				int index = 0;
				row.createCell(index++).setCellValue(entity.getLoginName());
				row.createCell(index++).setCellValue(entity.getUsername());
				row.createCell(index++).setCellValue(entity.getType());
				row.createCell(index++).setCellValue(entity.getEmail());
				row.createCell(index++).setCellValue(entity.getPhone());
				
				rowIndex++;
			}
		}
		
		// 下载文件
		HgoPoiUtils.downLoadExcelXlsx(xwb, "用户列表.xlsx");
	}

	/**
	 * 下载导入模板
	 * @throws Exception
	 */
	public void exportTemplate() throws Exception {
		// 列标题
		String[] headerArr = {"年级", "系部", "班主任姓名", "评语"};
		
		//-------------- 下拉框数据开始 --------------//
		// 年级
		List<String> gradeList = new ArrayList<String>();
		gradeList.add("2013年");
		gradeList.add("2014年");
		gradeList.add("2015年");
		gradeList.add("2016年");
		gradeList.add("2017年");
		gradeList.add("2018年");
		
		String[] gradeArr = null;
		if (gradeList!=null && gradeList.isEmpty()==false) {
			gradeArr = new String[gradeList.size()];
			for (int i=0; i<gradeList.size(); i++) {
				gradeArr[i] = gradeList.get(i);
			}
		}
		
		// 系部
		List<String> departmentList = new ArrayList<String>();
		departmentList.add("计算机系");
		departmentList.add("酒店系");
		departmentList.add("经贸系");
		
		String[] departmentArr = null;
		if (departmentList!=null && departmentList.isEmpty()==false) {
			departmentArr = new String[departmentList.size()];
			for (int i=0; i<departmentList.size(); i++) {
				departmentArr[i] = departmentList.get(i);
			}
		}
		
		// 下拉框
		ArrayList<String[]> selectDataList = new ArrayList<String[]>();
		selectDataList.add(gradeArr);
		selectDataList.add(departmentArr);
		
		// 下拉的列序号数组(序号从0开始)
		String[] selectColArr = {"0", "1"};
		//-------------- 下拉框数据结束 --------------//
		
		//-------------- 模拟数据开始 --------------//
		ArrayList<String[]> demoList = new ArrayList<String[]>();
		String[] dataArr1 = {"2013年", "计算机系", "张三", "任性"};
		String[] dataArr2 = {"2014年", "酒店系", "李四", "傲娇"};
		demoList.add(dataArr1);
		demoList.add(dataArr2);
		//-------------- 模拟数据结束 --------------//
		
		// 设置属性
		HgoExcelTemplateEntity entity = new HgoExcelTemplateEntity();
		entity.setFileName("导入模板.xlsx");			// 模版名称
		entity.setSheet1Name("Sheet1");				// sheet1名称
		entity.setSheet2Name("Sheet2");				// sheet2名称
		entity.setHeaderArr(headerArr);				// 表头
		entity.setDemoList(demoList);				// 样例数据
		entity.setSelectDataList(selectDataList);	// 下拉数据
		entity.setSelectColArr(selectColArr);		// 指定sheet1中需要下拉的列
		entity.setColumnWidth(4000);				// 列宽
		entity.setMaxRow(5000);						// 下拉数据来源作用于sheet1的最大行
		
		// 下载文件
		HgoPoiUtils.exportTemplateExcel(entity);
	}

	/**
	 * 导入Excel（普通）
	 * @param file
	 * @throws Exception
	 */
	public String importExcel(MultipartFile file) throws Exception {
		InputStream inputStream = file.getInputStream();
		Workbook wb = WorkbookFactory.create(inputStream);
		Sheet sheet = wb.getSheetAt(0);
		
		StringBuffer errorMsg = new StringBuffer();
		List<HgoUserInfo> entitylist = new ArrayList<HgoUserInfo>();
		
		Cell cell = null;
		for (Row row : sheet) {
			// 跳过第一行的表头
			if (row.getRowNum()==0) {
				continue;
			}
			
			int rowNum = row.getRowNum() + 1;
			
			cell = row.getCell(0);
			if (cell==null) {
				errorMsg.append("第" + rowNum + "行：未填写<br/>");
				continue;
			}
			String loginName = HgoPoiUtils.getCellValue(cell);
			System.out.println(loginName);
			
			cell = row.getCell(1);
			if (cell==null) {
				errorMsg.append("第" + rowNum + "行：未填写<br/>");
				continue;
			}
			String username = HgoPoiUtils.getCellValue(cell);
			System.out.println(username);
			
			cell = row.getCell(2);
			if (cell==null) {
				errorMsg.append("第" + rowNum + "行：未填写<br/>");
				continue;
			}
			String email = HgoPoiUtils.getCellValue(cell);
			System.out.println(email);
			
			// 保存有效数据
			HgoUserInfo entity = new HgoUserInfo();
			entity.setId(UUID.randomUUID().toString().replace("-", ""));
			entity.setLoginName(loginName);
			entity.setPassword(new Md5Hash("123456").toString());
			entity.setUsername(username);
			entity.setEmail(email);
			entity.setStatus(1);
			
			entitylist.add(entity);
		}
		
		// 插入数据库
		if (entitylist!=null && entitylist.isEmpty()==false) {
			iHgoUserInfoDAO.batchInsert(entitylist);
		}
		
		inputStream.close();
		return errorMsg.toString();
	}
	
	/**
	 * 导入Excel（封装成实体对象）
	 * @param list
	 * @return
	 * @throws Exception
	 * @throws IOException 
	 */
	public String importExcelEx(List<HgoPoiDemo> list) throws Exception {
		StringBuffer errorMsg = new StringBuffer();
		
		for (HgoPoiDemo hgoPoiDemo : list) {
			System.out.println(hgoPoiDemo.getLoginName());
			System.out.println(hgoPoiDemo.getUsername());
			System.out.println(hgoPoiDemo.getEmail());
		}
		
		return errorMsg.toString();
	}
	
}